USE [ATAChietkhau]
GO

/****** Object:  StoredProcedure [dbo].[sprTheodoiATAFastDaiLyReport]    Script Date: 07/28/2010 23:25:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sprTheodoiATAFastDaiLyReport]
	-- Add the parameters for the stored procedure here
	@BranchID uniqueidentifier, 
	@Month INT,
	@Year INT,
	@BrandID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @vReportDate DATETIME
	SELECT @vReportDate = dbo.Date(@Year, @Month, 1)

	DECLARE @TrungTamName NVARCHAR(255)
	DECLARE @TrungTamCode NVARCHAR(20)

	SELECT @TrungTamName = BranchName, @TrungTamCode = BranchCode FROM vwMisaBranches WHERE BranchID = @BranchID

	DECLARE @BrandName NVARCHAR(50)
	DECLARE @BrandCode NVARCHAR(50)
	SELECT @BrandCode = BrandCode, @BrandName = BrandName FROM dbo.ThuongHieu WHERE BrandID = @BrandID

    -- Insert statements for procedure here
	CREATE TABLE #tblTheodoiATAFastDaiLyReport
	(
		TenTrungTam NVARCHAR(255),
		KhachHangID UNIQUEIDENTIFIER,
		MaKH NVARCHAR(100),
		TenKH NVARCHAR(100),
		LEVEL INT,
		ParentKhachHangID UNIQUEIDENTIFIER,
		TenChuongTrinh NVARCHAR(200),
		Thang INT,
		Nam INT,
		MucChietKhauTD DECIMAL,
		MucChietKhauCC DECIMAL,
		TenThuongHieu NVARCHAR(10),
		DuThuongID UNIQUEIDENTIFIER,
		StartDate DATETIME,
		DoanhSoCamKet MONEY,
		GhiChu NVARCHAR(255)
		--DoanhSoJUT MONEY,
		--DoanhSoHIL MONEY,
		--DoanhSoFAL MONEY,
		--DoanhSoVIE MONEY,
		--DoanhSoJAJ MONEY,
		--DoanhSoATA MONEY,
		--TongDoanhSo MONEY
	)


	INSERT INTO #tblTheodoiATAFastDaiLyReport(
	TenTrungTam, 
	KhachHangID, 
	MaKH, 
	TenKH, 
	LEVEL, 
	ParentKhachHangID, 
	TenChuongTrinh, 
	Thang, 
	Nam, 
	TenThuongHieu, 
	DuThuongID, 
	StartDate, 
	MucChietKhauTD, 
	MucChietKhauCC)
	SELECT 
	@TrungTamName AS TenTrungTam, 
	D.KhachHangID, 
	D.MaKH, 
	D.TenKH, 
	D.Level, 
	D.ParentID AS ParentKhachHangID, 
	A.TenChuongTrinh, 
	@Month AS Thang, 
	@Year AS Nam, 
	@BrandName AS TenThuongHieu, 
	DuThuongID, 
	A.StartDate, 
	A.MucChietKhauTD, 
	A.MucChietKhauCC
	FROM vwAllCustomers D 
	LEFT JOIN (
	SELECT A.HoiVienID, C.TenChuongTrinh, B.BrandCode AS TenThuongHieu, 
	A.ID AS DuThuongID, A.StartDate, A.MucChietKhauTD, A.MucChietKhauCC
	FROM KhachHangDuthuong A, ThuongHieu B, ChuongTrinh C
	WHERE A.BrandID = B.BrandID 
	AND A.ChuongTrinhID = C.ChuongTrinhID
	AND A.StartDate <= @vReportDate
	AND DATEADD(mm, A.Duration, A.StartDate) > @vReportDate
	) A ON A.HoiVienID = D.KhachHangID
	WHERE 
	(D.BranchID = @BranchID OR D.MaKH LIKE @TrungTamCode + '%')
	AND D.LEVEL = 1

	-- Lay doanh so theo thuong hieu
	-- Jutuner
	--SELECT ds.AccountingObjectID, SUM(ds.DoanhSoTrongThang) AS DoanhSoTrongThang 
	--FROM vwGetAllDoanhSo ds, #tblTheodoiATAFastDaiLyReport kh
	--WHERE ds.PhanLoaiHang LIKE + 'JUN-%'
	--AND MONTH(ds.NgayHoaDon) = @Month AND YEAR(ds.NgayHoaDon) = @Year
	--GROUP BY ds.AccountingObjectID


	SELECT 
	khdt.*, 
	ISNULL(DoanhSoTong.DoanhSoTD, 0) AS DoanhSoTD,
	ISNULL(DoanhSoTong.DoanhSoCC, 0) AS DoanhSoCC,
	ISNULL(dt.DaThu, 0) AS DaThu
	FROM #tblTheodoiATAFastDaiLyReport khdt
	LEFT JOIN (
	SELECT ds.KhachHangID, SUM(ds.DoanhSoTD) AS DoanhSoTD, SUM(ds.DoanhSoCC) AS DoanhSoCC
	FROM vwGetTotalMonthlyRevenue ds, #tblTheodoiATAFastDaiLyReport kh
	WHERE ds.Thang = @Month AND ds.Nam = @Year
	AND ThuongHieu LIKE @BrandCode
	GROUP BY ds.KhachHangID) DoanhSoTong ON khdt.KhachHangID = DoanhSoTong.KhachHangID
	LEFT JOIN (
	SELECT AccountingObjectID, SUM(DebitAmount) AS DaThu
	FROM ATAHANOI.dbo.GeneralLedger gl
	WHERE 
	MONTH(PostedDate) = @Month AND YEAR(PostedDate) = @Year
	AND CorrespondingAccountNumber LIKE '13683%'
	GROUP BY AccountingObjectID) dt ON dt.AccountingObjectID = khdt.KhachHangID
	ORDER BY khdt.TenChuongTrinh DESC

	DROP TABLE #tblTheodoiATAFastDaiLyReport

	-- exec sprTheodoiATAFastDaiLyReport '4EB226E3-FB42-4AB4-B82E-7021D2322B40', 7, 2010, 1
END



GO


